Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Partial Class Mngt_Info_Data_Mine
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim dt As New DataTable
            Dim i As Short

            For i = 1 To 10
                Dim r As DataRow = dt.NewRow()
                dt.Rows.Add(r)
                r = Nothing
            Next

            Datagrid.DataSource = dt
            Datagrid.DataBind()
        End If
    End Sub

    Protected Sub btnGenerate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
        Dim dgi As DataGridItem
        Dim sbSelect As New StringBuilder
        Dim strSelect As String
        Dim dm As New DAOMain.CommonDAO
        Dim ctr As Short = 0

        sbSelect.Append("Select * From vw_rpt_datamine WHERE ")

        For Each dgi In Datagrid.Items
            Dim f As WebControls_DataMine = dgi.FindControl("DataMine1")

            If Not IsNothing(Session("advisor")) Then sbSelect.Append(" Advisor like " & dm.Q(Session("UserName") & "%") & " AND ")

            If Not IsNothing(f) Then
                If f.Filter <> "" Then
                    sbSelect.Append(f.Filter & " AND ")
                    ctr += 1
                End If
            End If
        Next


        If ctr > 0 Then
            strSelect = Left(sbSelect.ToString, Len(sbSelect.ToString) - 4) & " Order by policy_client_last_name, policy_client_first_name"
        Else
            If Not IsNothing(Session("advisor")) Then
                strSelect = "Select * From vw_rpt_datamine" & _
                        " Where Advisor like " & dm.Q(Session("UserName") & "%") & _
                        " Order by policy_client_last_name, policy_client_first_name"
            Else
                strSelect = "Select * From vw_rpt_datamine Order by policy_client_last_name, policy_client_first_name"
            End If

        End If

        Dim dt As DataTable = dm.ExecQuery(strSelect)
        Dim sDS As New DataSet

        sDS.Tables.Add(dt)
        RenderReport(sDS, "vw_rpt_datamine", "~/Management_Info/Datamine.rpt")

        sDS = Nothing

        dm = Nothing
        sbSelect = Nothing
        dgi = Nothing
    End Sub

    Private Sub RenderReport(ByVal sDS As DataSet, ByVal TableName As String, ByVal ReportName As String)
        Dim sConn As New Data.SqlClient.SqlConnection
        Dim sCom As New Data.SqlClient.SqlCommand
        Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)

        Try

            sDS.Tables(0).TableName = TableName

            Dim objReport As New ReportDocument

            'MsgBox(Server.MapPath(Session("sqlUseReport").ToString))
            objReport.Load(Server.MapPath(ReportName))

            'objReport.PrintOptions.PaperOrientation = PaperOrientation.Landscape
            objReport.SetDataSource(sDS)

            Dim expReport As ExportOptions
            expReport = objReport.ExportOptions
            expReport.FormatOptions = New PdfRtfWordFormatOptions 'PDF format option
            expReport.ExportFormatType = ExportFormatType.PortableDocFormat 'Set the format type of document
            Dim req As ExportRequestContext = New ExportRequestContext
            req.ExportInfo = expReport

            'Sets the report to HTML stream
            Dim st As System.IO.Stream
            st = objReport.FormatEngine.ExportToStream(req)

            objReport = Nothing 'Dispose the report

            'Stream content
            Response.ClearContent()
            Response.ClearHeaders()
            Response.ContentType = "application/pdf"

            Dim b(st.Length) As Byte
            st.Read(b, 0, st.Length) 'Read Stream
            Response.BinaryWrite(b) 'Write Stream
            st.Close() 'Close Stream
            st = Nothing 'Dispose

            Response.End()

        Catch ex As Exception

        Finally
            sConn.Close()
        End Try
    End Sub
End Class
